package org.mcjug.model;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class StatusData {
	private static final String TAG = StatusData.class.getSimpleName();

	private final static String DB_NAME = "timeline.db";
	private static final int DB_VERSION = 1;
	public static final String TABLE_NAME = "timeline";
	public static final String C_ID = BaseColumns._ID;
	public static final String C_CREATED_AT = "created_at";
	public static final String C_SOURCE = "source";
	public static final String C_TEXT = "txt";
	public static final String C_USER = "user";

	private static final String GET_ALL_ORDER_BY = C_CREATED_AT + " desc";
	private static final String[] MAX_CREATED_AT_COLUMNS = { "max("
			+ C_CREATED_AT + ")" };
	private static final String[] DB_TEXT_COLUMNS = { C_TEXT };

	class DbHelper extends SQLiteOpenHelper {
		private Context context;

		public DbHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "create table " + TABLE_NAME + " (" + C_ID
					+ " int primary key, " + C_CREATED_AT + " int, " + C_SOURCE
					+ " text, " + C_TEXT + " text, " + C_USER + " text)";
			db.execSQL(sql);
			Log.d(TAG, "onCreated sql: " + sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("drop table if exists " + TABLE_NAME);
			Log.d(TAG, "onUpdated");
			onCreate(db);
		}

	}

	private final StatusData.DbHelper dbHelper;

	public StatusData(Context context) {
		this.dbHelper = new StatusData.DbHelper(context);
		Log.i(TAG, "Initialized data");
	}

	public void close() {
		this.dbHelper.close();
	}

	public long insertOrIgnore(ContentValues values) {
		long retval = -1;
		Log.d(TAG, "insertOrIgnore on " + values);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			retval = db.insertWithOnConflict(TABLE_NAME, null, values,
					SQLiteDatabase.CONFLICT_IGNORE);
		} finally {
			db.close();
		}
		return retval;
	}

	public int update(long id, ContentValues values, String selection,
			String[] selectionArgs) {
		int retval = -1;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			if (id < 0) {
				retval = db
						.update(TABLE_NAME, values, selection, selectionArgs);
			} else {
				retval = db.update(TABLE_NAME, values, C_ID + "=" + id, null);
			}

		} finally {
			db.close();
		}
		return retval;
	}

	public Cursor getStatusUpdates() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		return db.query(TABLE_NAME, null, null, null, null, null,
				GET_ALL_ORDER_BY);
	}

	public long getLatestStatusCreatedAtTime() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		try {
			Cursor cursor = db.query(TABLE_NAME, MAX_CREATED_AT_COLUMNS, null,
					null, null, null, null);
			try {
				return cursor.moveToNext() ? cursor.getLong(0) : Long.MIN_VALUE;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	public String getStatusTextById(long id) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		try {
			Cursor cursor = db.query(TABLE_NAME, DB_TEXT_COLUMNS, C_ID + "="
					+ id, null, null, null, null, "1");
			try {
				return cursor.moveToNext() ? cursor.getString(0) : null;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	public Cursor query(long id, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		if (id < 0) {
			return db.query(TABLE_NAME, projection, selection, selectionArgs,
					null, null, sortOrder);
		} else {
			return db.query(TABLE_NAME, projection, C_ID + "=" + id, null,
					null, null, null);
		}
	}

	public int delete(long id, String selection, String[] selectionArgs) {
		int retval = -1;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			if (id < 0) {
				retval = db.delete(TABLE_NAME, selection, selectionArgs);
			} else {
				retval = db.delete(TABLE_NAME, C_ID + "=" + id, null);
			}

		} finally {
			db.close();
		}
		Intent i = new Intent();
		i.setAction(UpdateIntentService.BROADCAST_INTENT_NAME);
		dbHelper.context.sendBroadcast(i);
		return retval;
	}
}
